package com.william.jdbcplus.core.util;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.william.jdbcplus.common.enums.DbType;
import com.william.jdbcplus.core.toolkit.AssertUtil;
import com.william.jdbcplus.core.toolkit.StringUtil;
import com.william.jdbcplus.core.util.druid.SqlLog4jFilter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

/**
 * JDBC 工具类
 */
public class JdbcUtil {
    
    /**
     * 根据数据源获取 NamedParameterJdbcTemplate
     *
     * @param ds 数据源
     * @return
     */
    public static NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(DataSource ds) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate;
    }

    /**
     * 根据数据源获取 JdbcTemplate
     *
     * @param ds 数据源
     * @return
     */
    public static JdbcTemplate getJdbcTemplate(DataSource ds) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        return jdbcTemplate;
    }

    /**
     * 创建数据源
     *
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static DruidDataSource createDataSource(String url, String username, String password) {
        DruidDataSource ds = new DruidDataSource();
        // 数据库连接字符串
        ds.setUrl(url);
        // 用户名
        ds.setUsername(username);
        // 密码
        ds.setPassword(password);
        // druid初始化连接数量
        ds.setInitialSize(10);
        // 最小空闲连接数
        ds.setMinIdle(10);
        // 最大并发连接数
        ds.setMaxActive(200);
        // 配置获取连接等待超时的时间,单位毫秒
        ds.setMaxWait(60000);
        // 超过时间限制是否回收,对于建立时间超过removeAbandonedTimeout的连接强制关闭
        ds.setRemoveAbandoned(false);
        // 超过时间限制多长,单位秒
        ds.setRemoveAbandonedTimeout(1800);
        // 在回收连接的同时是否打印日志
        ds.setLogAbandoned(false);
        // 数据源连接失败后，不再重试连接
        ds.setBreakAfterAcquireFailure(false);
        // 数据源连接失败后，错误后重试次数
        ds.setConnectionErrorRetryAttempts(3);
        // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
        ds.setTimeBetweenEvictionRunsMillis(60000);
        // 配置一个连接在池中最小生存的时间,单位是毫秒:5分钟
        ds.setMinEvictableIdleTimeMillis(300000);
        // 配置一个连接在池中最长生存的时间,单位是毫秒:30分钟
        ds.setMaxEvictableIdleTimeMillis(1800000);
        // 申请连接的时候检测
        ds.setTestWhileIdle(false);
        // 申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能
        ds.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能
        ds.setTestOnReturn(false);
        // 连接池中的minIdle数量以内的连接,空闲时间超过minEvictableIdleTimeMillis,则会执行keepAlive操作
        ds.setKeepAlive(true);
        // 要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。
        // 在druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如说100
        ds.setMaxOpenPreparedStatements(100);
        ds.setValidationQuery(getValidationQuery(url));

        Properties properties = new Properties();
        properties.setProperty("remarks", "true");
        properties.setProperty("useInformationSchema", "true");
        ds.setConnectProperties(properties);

        List<Filter> filters = new ArrayList<Filter>();
        SqlLog4jFilter sqlLog4jFilter = new SqlLog4jFilter();
        // 不输出参数日志
        sqlLog4jFilter.setStatementLogEnabled(false);
        // 输出执行日志
        sqlLog4jFilter.setStatementExecutableSqlLogEnable(true);
        // 输出错误日志
        sqlLog4jFilter.setStatementLogErrorEnabled(true);
        // 输出结果集错误日志
        sqlLog4jFilter.setResultSetLogErrorEnabled(true);
        StatFilter statFilter = new StatFilter();
        filters.add(sqlLog4jFilter);
        filters.add(statFilter);
        ds.setProxyFilters(filters);
        return ds;
    }

    /**
     * 判断数据源是否可以连接
     *
     * @param ds
     * @return
     */
    public static boolean testDataSource(DataSource ds) {
        boolean result = true;
        Connection connection = null;
        try {
            connection = ds.getConnection();
        } catch (Exception e) {
            result = false;
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return result;
    }

    public static List<String> getTables(DataSource ds) {
        List<String> result = new LinkedList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            resultSet = connection.getMetaData().getTables(connection.getCatalog(), connection.getSchema(), null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                if (!tableName.contains("$") && !tableName.contains("/")) {
                    result.add(tableName);
                }
            }
        } catch (SQLException e) {
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return result;
    }

    public static boolean existTable(DataSource ds, String table) {
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            resultSet = connection.getMetaData().getTables(connection.getCatalog(), connection.getSchema(), null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                if (table.equalsIgnoreCase(tableName)) {
                    return true;
                }
            }
        } catch (SQLException e) {
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return false;
    }

    public static String getTableRemark(DataSource ds, String table) {
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            resultSet = connection.getMetaData().getTables(connection.getCatalog(), connection.getSchema(), null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                if (table.equalsIgnoreCase(tableName)) {
                    String tableRemarks = resultSet.getString("REMARKS");
                    if (StringUtil.isBlank(tableRemarks)) {
                        tableRemarks = tableName;
                    }
                    return tableRemarks;
                }
            }
        } catch (SQLException e) {
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return table;
    }

    public static DbType getDbType(final DataSource ds) {
        DbType dbType = DbType.OTHER;
        Connection connection = null;
        try {
            connection = ds.getConnection();
            dbType = getDbType(connection.getMetaData().getURL());
        } catch (SQLException e) {
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return dbType;
    }

    public static String getUrlUserAndUser(final DataSource ds) {
        Connection connection = null;
        try {
            connection = ds.getConnection();
            String url = connection.getMetaData().getURL();
            String username = connection.getMetaData().getUserName();
            return url + username;
        } catch (SQLException e) {
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return "";
    }

    public static DbType getDbType(final NamedParameterJdbcTemplate template) {
        DataSource ds = template.getJdbcTemplate().getDataSource();
        DbType dbType = DbType.OTHER;
        Connection connection = null;
        try {
            connection = ds.getConnection();
            dbType = getDbType(connection.getMetaData().getURL());
        } catch (SQLException e) {
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return dbType;
    }

    /**
     * 根据连接地址判断数据库类型
     *
     * @param jdbcUrl 连接地址
     * @return ignore
     */
    public static DbType getDbType(String jdbcUrl) {
        AssertUtil.isFalse(StringUtil.isEmpty(jdbcUrl), "Error: The jdbcUrl is Null, Cannot read database type");
        if (jdbcUrl.contains(":mysql:") || jdbcUrl.contains(":cobar:")) {
            return DbType.MYSQL;
        } else if (jdbcUrl.contains(":mariadb:")) {
            return DbType.MARIADB;
        } else if (jdbcUrl.contains(":oracle:")) {
            return DbType.ORACLE;
        } else if (jdbcUrl.contains(":sqlserver:") || jdbcUrl.contains(":microsoft:")) {
            return DbType.SQL_SERVER2005;
        } else if (jdbcUrl.contains(":sqlserver2012:")) {
            return DbType.SQL_SERVER;
        } else if (jdbcUrl.contains(":postgresql:")) {
            return DbType.POSTGRE_SQL;
        } else if (jdbcUrl.contains(":hsqldb:")) {
            return DbType.HSQL;
        } else if (jdbcUrl.contains(":db2:")) {
            return DbType.DB2;
        } else if (jdbcUrl.contains(":sqlite:")) {
            return DbType.SQLITE;
        } else if (jdbcUrl.contains(":h2:")) {
            return DbType.H2;
        } else if (jdbcUrl.contains(":dm:")) {
            return DbType.DM;
        } else {
            return DbType.OTHER;
        }
    }

    public static String getValidationQuery(String jdbcUrl) {
        DbType dbType = getDbType(jdbcUrl);
        if (dbType == DbType.DB2) {
            return "select 1 from sysibm.sysdummy1";
        }
        if (dbType == DbType.ORACLE) {
            return "select 1 from dual";
        }
        if (dbType == DbType.MYSQL) {
            return "select 1";
        }
        if (dbType == DbType.H2) {
            return "select 1";
        }
        if (dbType == DbType.HSQL) {
            return "select 1 from INFORMATION_SCHEMA.SYSTEM_USERS";
        }
        if (dbType == DbType.SQLITE) {
            return "select 1";
        }
        if (dbType == DbType.SQL_SERVER2005) {
            return "select 1";
        }
        if (dbType == DbType.SQL_SERVER) {
            return "select 1";
        }
        if (dbType == DbType.DM) {
            return "select 1";
        }
        if (dbType == DbType.MARIADB) {
            return "select 1";
        }

        return "select 1";
    }


    @SuppressWarnings("resource")
    public static void main(String[] args) {
        String mysqlUrl = "jdbc:mysql://192.168.0.98:3333/his_seq2";
        String mysqlUsername = "root";
        String mysqlPassword = "123456";
        DataSource mysqlDatasource = createDataSource(mysqlUrl, mysqlUsername, mysqlPassword);
        boolean flagMysql = testDataSource(mysqlDatasource);
        System.out.println(flagMysql);

        String oracleUrl = "jdbc:oracle:thin:@192.168.0.98:1521:ORCL";
        String oracleUsername = "HIS_SEQ2";
        String oraclePassword = "root";
        DataSource oracleDatasource = createDataSource(oracleUrl, oracleUsername, oraclePassword);
        boolean flagOracle = testDataSource(oracleDatasource);
        System.out.println(flagOracle);

        String sqlserverUrl = "jdbc:sqlserver://192.168.0.98:1433;DatabaseName=his_seq";
        String sqlserverUsername = "sa2";
        String sqlserverPassword = "lis";
        DataSource sqlserverDatasource = createDataSource(sqlserverUrl, sqlserverUsername, sqlserverPassword);
        boolean flagSqlserver = testDataSource(sqlserverDatasource);
        System.out.println(flagSqlserver);


    }
}